<?php

function upload_form(){
	$view = form_header( "Grade" , "Grade"  );
	
	$file = array(
		'name'=>'filexl',
		'id'=>'filexl',
		'type'=>'file','size'=>'45'
	);
	$form_file = form_dynamic($file);
	$view .= form_field_display( $form_file , "File Absen" );
	$submit = array(
		'value' => ( $id ==0 ? ' Simpan ' :'  Update  '),
		'name' => 'simpan', 
		'type'=>'submit','class'=>'main_button'
	);
	$form_submit= form_dynamic($submit);
	
	$view .= form_field_display( $form_submit, "&nbsp;" ,  "" );
	$view .= form_footer( );	
	return $view;
}


function validate(){

}

function upload_file_csv(){ 

	$query = "TRUNCATE TABLE a_upload_capture";
	$res = my_query($query);	
	
	$path 		= 'files/absensi/';
	$filename 	= strtolower($_FILES['filexl']['name']);
	$upload 	= move_uploaded_file($_FILES['filexl']['tmp_name'] , $path.$filename);
	if($upload){
		$t = 0;
		$handle = fopen($path.$filename, "r");
		while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
			if( $t>0)
			stor_data($data);
			 $t++;
		}
		stor_realisasi() ;
		fclose($handle);
	}
	return true;
}

function stor_data($data){
	$nik = $data[0];
	list($dd,$mm,$yyyy)  = explode("/", $data[2]);
	$tanggal = $yyyy.'-'.$mm.'-'.$dd;
	$datang =$data[3];
	$pulang =$data[4];
	$datas = array(
		'nik'=>my_type_data_str(sprintf('%07d' ,$nik)),
		'tanggal'=>my_type_data_str($tanggal),
		'datang'=>my_type_data_str($datang),
		'pulang'=>my_type_data_str($pulang),
		'datetime_added'=>my_type_data_function('NOW()') 
	);
	return my_insert_record('a_upload_capture',$datas);
}
function convert_xl_to_csv($file){

	return date("H:i",7200);
	$a= strtotime('2009-12-01 03:10:00');
	$b= strtotime('2009-12-01 04:16:00');
	return ($b-$a);
//	require_once 'phpExcelReader/Excel/reader.php';
	$data = new Spreadsheet_Excel_Reader();  
	$data->setOutputEncoding('CP1251');
	$data->read('absensi.xls');

	error_reporting(E_ALL ^ E_NOTICE);
	ob_start(); 
	for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
		for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
			echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
		}
		echo "\n";
	}
	
	$content = ob_get_clean(); 
	return $content;
}

/*
	======================= store to realisasi =======================
*/

function stor_realisasi(){
	$periode_id=get_periode_absen() ;
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	
	$query = "SELECT nik,tanggal,MIN(datang) as jam_datang,MAX(pulang) as jam_pulang FROM a_upload_capture 
		where (tanggal BETWEEN '{$periode['dari']}' AND '{$periode['sampai']}' ) 
		group by nik,tanggal 
		ORDER BY nik,tanggal" ;
	$res = my_query($query);
	$nik="x";

	while($ey = my_fetch_array($res)){
		if($ey['nik']<>$nik){
			$nik=$ey['nik'] ;
			$karyawan_id=check_karyawan_by_nik($nik) ;
		}
		$tanggal= strtotime($ey['tanggal']) ;
		$tgl=date('d', $tanggal);
		//var_dump($tgl);
		$query = "SELECT * FROM a_realisasi 
			WHERE karyawan_id = {$karyawan_id}
			AND field_tgl='tgl_{$tgl}' ";
		$dres = my_query($query);
		if(my_num_rows($dres) > 0){
			$reals = my_fetch_array($dres);
			$realisasi_id=$reals['realisasi_id'];
			$sc_jadwal_kerja_kelompok_id=(int) $reals['sc_jadwal_kerja_kelompok_id'];
		}else{
			$realisasi_id=$sc_jadwal_kerja_kelompok_id=0 ;
		}
		if($ey['jam_datang']=='00:00:00'){
			$jam_datang='';
		}else{	
			$jam_datang=$ey['jam_datang'] ;
		}	
		if($ey['jam_pulang']=='00:00:00'){
			$jam_pulang='';
		}else{	
			$jam_pulang=$ey['jam_pulang'] ;
		}	
			

		$datas = array(
			'karyawan_id'=>my_type_data_int($karyawan_id), 	
			'field_tgl' =>my_type_data_str('tgl_'. $tgl) ,	
			'jam_datang' =>my_type_data_str($jam_datang),	
			'jam_pulang'=>my_type_data_str($jam_pulang),
//			'status_absen_id'=>my_type_data_int($status), 
//			'sc_jadwal_kerja_kelompok_id'=>my_type_data_int($sc_jadwal_kerja_kelompok_id), 	
		);
//var_dump($datas);
		if($realisasi_id >0 ){
			my_update_record('a_realisasi', 'realisasi_id' , $realisasi_id , $datas);
		}else{
			//var_dump($datas); exit;
			my_insert_record('a_realisasi',$datas);
		}	
	}	
		//var_dump($datas); exit;	
/*	
	$tgl=date('d', strtotime($date));
	$query = "SELECT * FROM a_realisasi 
		WHERE karyawan_id = {$karyawan_id}
		AND field_tgl='tgl_{$tgl}' ";
	$res = my_query($query);
	if(my_num_rows($res) > 0){
		$reals = my_fetch_array($res);
		$realisasi_id=$reals['realisasi_id'];
		$sc_jadwal_kerja_kelompok_id=(int) $reals['sc_jadwal_kerja_kelompok_id'];
	}else{
		$realisasi_id=$sc_jadwal_kerja_kelompok_id=0 ;
	}
	if($sc_jadwal_kerja_kelompok_id==0){	
		$query = "SELECT * FROM a_jadwal_kerja_kelompok 
			WHERE 	waktu_kerja_id = {$waktu_kerja_id}
			AND kelompok='{$kelompok1}' ";
		$res = my_query($query);
		$reals = my_fetch_array($res);
		$sc_jadwal_kerja_kelompok_id=(int) $reals['jadwal_kerja_kelompok_id'];
	}	
	$status=get_status_absen_id( $karyawan_id, $date,0);
	$check_file_upload = get_absen_upload($nik,$date); 
	
	if($check_file_upload['datang']=='00:00:00'){
		$jam_datang='';
	}else{	
		$jam_datang=$check_file_upload['datang'] ;
	}
	if($check_file_upload['pulang']=='00:00:00'){
		$jam_pulang='';
	}else{	
		$jam_pulang=$check_file_upload['pulang'] ;
	}
	
	if($status==0){
		if($jam_datang=='' AND $jam_pulang==''){
			if($libur){
				$status=2 ; //X
			}else{
				$status=16 ; //A
			}
		}else{
			if($kelompok=="1" OR 
				$kelompok=="2" OR
				$kelompok=="3"){
					$status=(int) $kelompok+2 ; //1,2,3	
			}else{
				$status=1 ; //N
			}
		}
	}	
	$datas = array(
			'karyawan_id'=>my_type_data_int($karyawan_id), 	
			'field_tgl' =>my_type_data_str('tgl_'. date('d', strtotime($date)) ),	
			'jam_datang' =>my_type_data_str($jam_datang),	
			'jam_pulang'=>my_type_data_str($jam_pulang),
			'status_absen_id'=>my_type_data_int($status), 
			'sc_jadwal_kerja_kelompok_id'=>my_type_data_int($sc_jadwal_kerja_kelompok_id), 	
	);
	if($realisasi_id >0 ){
		my_update_record('a_realisasi', 'realisasi_id' , $realisasi_id , $datas);
		return $realisasi_id;
	}
	return my_insert_record('a_realisasi',$datas);
	*/
}






function  get_absen_upload($nik,$date){
	$res = array();
	$res['datang'] = get_smallesttime($nik,$date);
	$res['pulang'] = get_bigesttime($nik,$date);
	return  $res ;
}

function get_smallesttime($nik,$tanggal){
	$query = "SELECT datang FROM a_upload_capture WHERE nik ='{$nik}' AND tanggal ='{$tanggal}' ORDER BY datang ASC LIMIT 1";
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['datang'];
}

function get_bigesttime($nik,$tanggal){
	$query = "SELECT pulang FROM a_upload_capture WHERE nik ='{$nik}' AND tanggal ='{$tanggal}' ORDER BY pulang DESC LIMIT 1";
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['pulang'];
}
